﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using YmatouSeller.Common.Dapper;
using YmatouSeller.Notice.Common;
using YmatouSeller.Notice.Repository.Interface;
using YmatouSeller.NoticeAPI.Model.Entity;
using YmatouSeller.NoticeAPI.Model.Enums;

namespace YmatouSeller.Notice.Repository.Implement
{
    public class SystemNoticeDALService : ISystemNoticeDALService
    {
        private string YmtSellerinfoWriteConnectionString = ConnectionStringHelper.WriteConnection();
        private string YmtSellerinfoReadConnectionString = ConnectionStringHelper.ReadConnection();
        private string YmtReleaseReadConnectionString = ConnectionStringHelper.ReadConnection("YmtReleaseReadConnectionString");

        #region 消息通知管理

        #region

        /// <summary>
        /// 根据用户名或Email获取用户ID
        /// </summary>
        /// <param name="loginIds"></param>
        /// <param name="accoutType"></param>
        /// <returns></returns>
        private List<UserInfo> GetUserIDsByLoginIds(List<string> loginIds, int accoutType)
        {
            if (loginIds == null)
            {
                return null;
            }
            List<UserInfo> result = null;
            using (IDbConnection conn = new SqlConnection(YmtReleaseReadConnectionString))
            {
                var dynParam = new DynamicParameters(new { });
                var fields = new List<string>();
                for (var i = 0; i < loginIds.Count; i++)
                {
                    var fieldName = string.Format("@p{0}", i);
                    fields.Add(fieldName);
                    if (accoutType == 1)
                    {
                        dynParam.Add(fieldName, loginIds[i], DbType.Int32);
                    }
                    else
                    {
                        dynParam.Add(fieldName, loginIds[i]);
                    }
                }

                string sql = string.Format(@"SELECT UserID=iUserId,LoginID=sLoginId FROM [dbo].[Ymt_Users] WITH(NOLOCK) WHERE bLocked<>1 AND sLoginId IN({0})", string.Join(",", fields));
                if (accoutType == 1)
                {
                    sql = string.Format(@"SELECT UserID=iUserId,LoginID=sLoginId FROM [dbo].[Ymt_Users] WITH(NOLOCK) WHERE bLocked<>1 AND iUserId IN({0})", string.Join(",", fields));
                }
                result = conn.Query<UserInfo>(sql, dynParam).ToList();
            }

            return result;
        }

        /// <summary>
        /// 通知消息更新SQL
        /// </summary>
        /// <param name="detailModle"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private string GetSaveSystemNoticeSQL(ref SystemNoticeDetail detailModle, ref DynamicParameters param)
        {
            string sql = "";
            if (string.IsNullOrEmpty(detailModle.NoticeId))
            {
                detailModle.NoticeId = Guid.NewGuid().ToString();
                sql = @" INSERT INTO [dbo].[Ymt_SystemNotice]
                               ([NoticeId]
                               ,[NoticeTitle]
                               ,[NoticeType]
                               ,[NoticeTarget]
                               ,[NoticeUrl]
                               ,[NoticeContext]
                               ,[NoticeRollingConfig]
                               ,[Operator]
                               ,[PublishTime]
                               ,[IsDeleted]
                               ,[CreateTime]
                               ,[LastUpdateTime]
                               ,[TargetType])
                         VALUES
                               (@NoticeId
                               ,@NoticeTitle
                               ,@NoticeType
                               ,@NoticeTarget
                               ,@NoticeUrl
                               ,@NoticeContext
                               ,@NoticeRollingConfig
                               ,@Operator
                               ,GETDATE()
                               ,0
                               ,GETDATE()
                               ,GETDATE()
                               ,@TargetType); ";
            }
            else
            {
                sql = @" UPDATE [dbo].[Ymt_SystemNotice]
                           SET [NoticeTitle] = @NoticeTitle
                              ,[NoticeType] = @NoticeType
                              ,[NoticeTarget] = @NoticeTarget
                              ,[NoticeUrl] = @NoticeUrl
                              ,[NoticeContext] = @NoticeContext
                              ,[NoticeRollingConfig] = @NoticeRollingConfig
                              ,[Operator] = @Operator
                              ,[LastUpdateTime] =GETDATE()
                         WHERE [NoticeId]=@NoticeId; ";
            }
            param.Add("NoticeId", detailModle.NoticeId);
            return sql;
        }

        /// <summary>
        /// 保存通知对象
        /// </summary>
        /// <param name="detailModle"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private bool SaveNoticeTarget(SystemNoticeDetail detailModle)
        {
            if (detailModle.TargetList == null || detailModle.TargetList.Count <= 0)
            {
                return false;
            }
            var param = new DynamicParameters(new { NoticeId = detailModle.NoticeId });
            using (var conn = new System.Data.SqlClient.SqlConnection(YmtSellerinfoWriteConnectionString))
            {
                try
                {
                    var sql =
                        " UPDATE [dbo].[Ymt_SystemNotice_Target] SET [IsDeleted]=1,[LastUpdateTime]=GETDATE() WHERE [NoticeId] = @NoticeId; ";
                    DapperHelper.ExcuteSql(YmtSellerinfoWriteConnectionString, sql, param);
                    CopyData(detailModle.TargetList, detailModle.NoticeId, conn);
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 批量添加
        /// </summary>
        /// <param name="targetList"></param>
        /// <param name="conn"></param>
        private void CopyData(List<string> targetList, string noticeId, SqlConnection conn)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("NoticeId");
            dt.Columns.Add("NoticeTargetDetail");

            foreach (var target in targetList)
            {
                DataRow r = dt.NewRow();
                r["NoticeId"] = noticeId;
                r["NoticeTargetDetail"] = target;
                dt.Rows.Add(r);
            }

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            int tolCount = targetList.Count;
            using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, null))
            {
                copy.BatchSize = tolCount;
                copy.DestinationTableName = "Ymt_SystemNotice_Target";
                copy.ColumnMappings.Add("NoticeId", "NoticeId");
                copy.ColumnMappings.Add("NoticeTargetDetail", "NoticeTargetDetail");

                copy.WriteToServer(dt);
            }
        }

        /// <summary>
        /// 通知附件SQL
        /// </summary>
        /// <param name="detailModle"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private string GetAttachmentSql(ref SystemNoticeDetail detailModle, ref DynamicParameters param)
        {
            var attachmentSql = "";
            if (detailModle.AttachmentList != null && detailModle.AttachmentList.Count > 0)
            {
                string noticeId = detailModle.NoticeId;
                detailModle.AttachmentList.ForEach(x =>
                {
                    x.NoticeId = noticeId;
                });
                int idx = 0;
                foreach (var atta in detailModle.AttachmentList)
                {
                    var optionTypeName = string.Format("@OptionType{0}", idx);
                    var fileNameName = string.Format("@FileName{0}", idx);
                    var fileUrlName = string.Format("@FileUrl{0}", idx);

                    param.Add(optionTypeName, atta.OptionType);
                    param.Add(fileNameName, atta.FileName);
                    param.Add(fileUrlName, atta.FileUrl);
                    if (atta.NoticeOptionId <= 0)
                    {
                        attachmentSql += string.Format(@" INSERT INTO [dbo].[Ymt_SystemNotice_Attachment]
                                                   ([NoticeId]
                                                   ,[OptionType]
                                                   ,[FileName]
                                                   ,[FileUrl]
                                                   ,[CreateTime]
                                                   ,[LastUpdateTime])
                                             VALUES
                                                   (@NoticeId
                                                   ,{0}
                                                   ,{1}
                                                   ,{2}
                                                   ,GETDATE()
                                                   ,GETDATE()); ", optionTypeName, fileNameName, fileUrlName);

                    }
                    else
                    {
                        var noticeOptionIdName = string.Format("@NoticeOptionId{0}", idx);
                        param.Add(noticeOptionIdName, atta.NoticeOptionId);
                        attachmentSql += string.Format(@" UPDATE [dbo].[Ymt_SystemNotice_Attachment]
                                               SET [NoticeId] = @NoticeId
                                                  ,[OptionType] = {0}
                                                  ,[FileName] = {1}
                                                  ,[FileUrl] = {2}
                                                  ,[LastUpdateTime] = GETDATE()
                                             WHERE [NoticeOptionId]={3}; ", optionTypeName, fileNameName, fileUrlName, noticeOptionIdName);
                    }
                    idx++;
                }
            }
            return attachmentSql;
        }

        #endregion

        /// <summary>
        /// 获取国家信息
        /// </summary>
        /// <returns></returns>
        public List<CountryInfo> GetAllCountry(string continent, int countryID, int selectType)
        {
            try
            {
                DynamicParameters dynParam = null;
                var sql = @"SELECT iCountryId CountryId,sCountryName CountryNameEn,sCountryNameZh CountryNameZh,sContinent Continent FROM dbo.Ymt_Country WITH(NOLOCK) ";
                if (selectType == 0)
                {
                    sql = @"SELECT DISTINCT sContinent Continent FROM dbo.Ymt_Country WITH(NOLOCK) ";
                }
                else if (selectType == 1)
                {
                    if (!string.IsNullOrEmpty(continent))
                    {
                        sql += " WHERE sContinent=@Continent ";
                        dynParam = new DynamicParameters();
                        dynParam.Add("Continent", continent);
                    }
                }
                else
                {
                    if (countryID > -1)
                    {
                        sql += " WHERE iCountryId=@CountryId ";
                        dynParam = new DynamicParameters();
                        dynParam.Add("CountryId", countryID);
                    }
                }
                using (var conn = new SqlConnection(YmtReleaseReadConnectionString))
                {
                    var list = conn.Query<CountryInfo>(sql, dynParam).ToList();
                    return list;
                }
            }
            catch (Exception ex) { throw ex; }
        }

        /// <summary>
        /// 根据用户名获取用户ID
        /// </summary>
        /// <param name="loginIds"></param>
        /// <returns></returns>
        public List<UserInfo> GetUserIDLstByLoginIds(List<string> loginIds, int accoutType)
        {
            if (loginIds == null || loginIds.Count <= 0)
            {
                return null;
            }

            List<UserInfo> list = new List<UserInfo>();

            try
            {
                if (loginIds.Count > 2000)
                {
                    var pageCount = loginIds.Count / 2000 + (loginIds.Count % 2000 > 0 ? 1 : 0);
                    for (var i = 0; i < pageCount; i++)
                    {
                        var _mails = loginIds.Skip(i * 2000).Take(2000).ToList();
                        var users = GetUserIDsByLoginIds(_mails, accoutType);
                        if (users != null && users.Count > 0)
                            list.AddRange(users);
                    }
                }
                else
                {
                    var users = GetUserIDsByLoginIds(loginIds, accoutType);
                    if (users != null && users.Count > 0)
                        list.AddRange(users);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        /// <summary>
        /// 删除通知附件
        /// </summary>
        /// <param name="detailModle"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool DeleteNoticeAttachment(List<long> noticeOptionIds, string noticeId)
        {
            if (string.IsNullOrEmpty(noticeId) && (noticeOptionIds == null || noticeOptionIds.Count <= 0))
            {
                return false;
            }

            var param = new DynamicParameters(new { });
            string whereSQL = "";
            if (!string.IsNullOrEmpty(noticeId))
            {
                whereSQL += " AND NoticeId=@NoticeId ";
                param.Add("NoticeId", noticeId);
            }
            if (noticeOptionIds != null && noticeOptionIds.Count > 0)
            {
                var fields = new List<string>();
                for (var i = 0; i < noticeOptionIds.Count; i++)
                {
                    var fieldName = string.Format("@p{0}", i);
                    fields.Add(fieldName);
                    param.Add(fieldName, noticeOptionIds[i]);
                }
                whereSQL += string.Format(@" AND [NoticeOptionId] IN({0}) ", string.Join(",", fields));
            }
            if (string.IsNullOrEmpty(whereSQL)) return false;

            var sql =
                string.Format(
                    @" UPDATE [dbo].[Ymt_SystemNotice_Attachment] SET [IsDeleted]=1,[LastUpdateTime]=GETDATE() WHERE 1=1 {0};",
                    whereSQL);

            return DapperHelper.ExcuteSql(YmtSellerinfoWriteConnectionString, sql, param);
        }

        /// <summary>
        /// 保存通知消息
        /// </summary>
        /// <param name="detailModle"></param>
        /// <returns></returns>
        public bool SaveSystemNotice(SystemNoticeDetail detailModle)
        {
            var param = new DynamicParameters(new { });
            param.Add("NoticeTitle", detailModle.NoticeTitle);
            param.Add("NoticeType", detailModle.NoticeType);
            param.Add("NoticeTarget", detailModle.NoticeTarget);
            param.Add("NoticeUrl", detailModle.NoticeUrl);
            param.Add("NoticeContext", detailModle.NoticeContext);
            param.Add("NoticeRollingConfig", detailModle.NoticeRollingConfig);
            param.Add("Operator", detailModle.Operator);
            param.Add("TargetType", detailModle.TargetType);

            var sql = GetSaveSystemNoticeSQL(ref detailModle, ref param);
            var attachmentSql = GetAttachmentSql(ref detailModle, ref param);
            try
            {
                var flag = DapperHelper.ExcuteSql(YmtSellerinfoWriteConnectionString, sql + attachmentSql, param);
                if (flag)
                {
                    flag = SaveNoticeTarget(detailModle);
                }
                return flag;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取通知消息
        /// </summary>
        /// <param name="noticeId"></param>
        /// <returns></returns>
        public SystemNoticeDetail GetSystemNoticeItem(string noticeId)
        {
            SystemNoticeDetail result = new SystemNoticeDetail();
            var sql = @"SELECT [NoticeId]
                          ,[NoticeTitle]
                          ,[NoticeType]
                          ,[NoticeTarget]
                          ,[NoticeUrl]
                          ,[NoticeContext]
                          ,[NoticeRollingConfig]
                          ,[Operator]
                          ,[PublishTime]
                          ,[IsDeleted]
                          ,[CreateTime]
                          ,[LastUpdateTime]
                          ,[NoticeNum]
                          ,[TargetType]
                      FROM [dbo].[Ymt_SystemNotice] WITH(NOLOCK)
                      WHERE [NoticeId] = @NoticeId; 
                      SELECT [NoticeOptionId]
                            ,[NoticeId]
                            ,[OptionType]
                            ,[FileName]
                            ,[FileUrl]
                            ,[CreateTime]
                            ,[LastUpdateTime]
                        FROM [dbo].[Ymt_SystemNotice_Attachment] WITH(NOLOCK)
                        WHERE [IsDeleted]=0 AND [NoticeId] = @NoticeId; 
                      SELECT [NoticeTargetDetail]
                        FROM [dbo].[Ymt_SystemNotice_Target] WITH(NOLOCK)
                        WHERE [IsDeleted]=0 AND [NoticeId] = @NoticeId; ";
            var param = new DynamicParameters(new { NoticeId = noticeId });
            try
            {
                using (var conn = new System.Data.SqlClient.SqlConnection(YmtSellerinfoReadConnectionString))
                {
                    var multi = conn.QueryMultiple(sql, param);
                    result = multi.Read<SystemNoticeDetail>().FirstOrDefault();
                    if (result != null)
                    {
                        result.NoticeTypeStr = result.NoticeType.ToString();
                        result.AttachmentList = multi.Read<SystemNoticeAttachment>().ToList();
                        result.PublishTimeStr = result.PublishTime.ToString();
                        if (result.AttachmentList != null && result.AttachmentList.Count(x => x.OptionType == 2) > 0)
                        {
                            result.HasAttachment = true;
                        }
                        result.TargetList = multi.Read<string>().ToList();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 获取通知消息
        /// </summary>
        /// <param name="noticeId"></param>
        /// <returns></returns>
        public List<SystemNoticeAttachment> GetSystemNoticeAttachmentList(List<string> noticeIds)
        {
            List<SystemNoticeAttachment> result = new List<SystemNoticeAttachment>();
            var param = new DynamicParameters(new { });
            var fields = new List<string>();
            for (var i = 0; i < noticeIds.Count; i++)
            {
                var fieldName = string.Format("@p{0}", i);
                fields.Add(fieldName);
                param.Add(fieldName, noticeIds[i]);
            }
            var sql = string.Format(@"SELECT [NoticeOptionId]
                            ,[NoticeId]
                            ,[OptionType]
                            ,[FileName]
                            ,[FileUrl]
                            ,[CreateTime]
                            ,[LastUpdateTime]
                        FROM [dbo].[Ymt_SystemNotice_Attachment] WITH(NOLOCK)
                        WHERE [IsDeleted]=0 AND [NoticeId] IN({0});", string.Join(",", fields));
            try
            {
                result = DapperHelper.QueryData<SystemNoticeAttachment>(YmtSellerinfoReadConnectionString, sql, param);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 获取通知消息
        /// </summary>
        /// <param name="noticeId"></param>
        /// <returns></returns>
        public List<SystemNoticeTarget> GetSystemNoticeTargetList(List<string> noticeIds)
        {
            List<SystemNoticeTarget> result = new List<SystemNoticeTarget>();
            var param = new DynamicParameters(new { });
            var fields = new List<string>();
            for (var i = 0; i < noticeIds.Count; i++)
            {
                var fieldName = string.Format("@p{0}", i);
                fields.Add(fieldName);
                param.Add(fieldName, noticeIds[i]);
            }
            var sql = string.Format(@"SELECT [NoticeDetailId]
                            ,[NoticeId]
                            ,[NoticeTargetDetail]
                            ,[CreateTime]
                            ,[LastUpdateTime]
                        FROM [dbo].[Ymt_SystemNotice_Target] WITH(NOLOCK)
                        WHERE [IsDeleted]=0 AND [NoticeId] IN({0});", string.Join(",", fields));
            try
            {
                result = DapperHelper.QueryData<SystemNoticeTarget>(YmtSellerinfoReadConnectionString, sql, param);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 获取通知消息
        /// </summary>
        /// <param name="noticeTitle"></param>
        /// <param name="noticeType"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<SystemNotice> GetSystemNoticeList(string noticeTitle, NoticeTypeEnum noticeType, DateTime? beginTime, DateTime? endTime, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = 0;
            var param = new DynamicParameters(new { });
            var start = (pageIndex - 1) * pageSize + 1;
            var end = pageIndex * pageSize;
            param.Add("StartRow", start);
            param.Add("EndRow", end);

            List<SystemNotice> result = new List<SystemNotice>();
            string queryWhere = "";
            if (!string.IsNullOrEmpty(noticeTitle))
            {
                queryWhere += " AND CHARINDEX(@NoticeTitle,NoticeTitle)>0 ";
                param.Add("NoticeTitle", noticeTitle);
            }
            if (noticeType > 0)
            {
                queryWhere += " AND NoticeType=@NoticeType ";
                param.Add("NoticeType", noticeType);
            }
            if (beginTime.HasValue)
            {
                queryWhere += " AND CreateTime>=@BeginTime ";
                param.Add("BeginTime", beginTime.Value);
            }
            if (endTime.HasValue)
            {
                queryWhere += " AND CreateTime<=@EndTime ";
                param.Add("EndTime", endTime.Value);
            }

            var tmpSql =
                string.Format(@" SELECT T2.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY CreateTime DESC) RowNum ,T1.* FROM (SELECT [NoticeId]
                                              ,[NoticeTitle]
                                              ,[NoticeType]
                                              ,[NoticeTarget]
                                              ,[NoticeUrl]
                                              ,[NoticeContext]
                                              ,[NoticeRollingConfig]
                                              ,[Operator]
                                              ,[PublishTime]
                                              ,[IsDeleted]
                                              ,[CreateTime]
                                              ,[LastUpdateTime]
                                              ,[NoticeNum]
                                              ,[TargetType]
                                          FROM [dbo].[Ymt_SystemNotice] WITH(NOLOCK)
                                          WHERE 1=1 {0}) T1) T2 WHERE RowNum BETWEEN @StartRow AND @EndRow; ",
                    queryWhere);

            var totalCountSql =
                string.Format("SELECT COUNT(0) FROM [dbo].[Ymt_SystemNotice] WITH(NOLOCK) WHERE 1=1 {0};", queryWhere);
            try
            {
                using (var conn = new System.Data.SqlClient.SqlConnection(YmtSellerinfoReadConnectionString))
                {
                    var multi = conn.QueryMultiple(totalCountSql + tmpSql, param);
                    totalCount = multi.Read<int>().FirstOrDefault();
                    result = multi.Read<SystemNotice>().ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #endregion


        #region 买手后台--通知中心

        public int GetUnReadNoticeCount(string continent, int sellerid)
        {
            int result = 0;
            var param = new DynamicParameters(new { });
            param.Add("continent", continent);
            param.Add("sellerid", sellerid.ToString());
            var sql = string.Format(@"select count(1) from Ymt_SystemNotice_Target (nolock) 
                                        where  (NoticeTargetDetail =@continent or NoticeTargetDetail=@sellerid)
                                        and NoticeId not in (select NoticeId from Ymt_SystemNotice_Read (nolock) where SellerId=@sellerid)  and isdeleted=0");
            result = DapperHelper.QueryData<int>(YmtSellerinfoReadConnectionString, sql, param).FirstOrDefault();
            return result;
        }

        public List<SystemNotice> GetMoveNoticeList(string continent, int sellerid)
        {
            List<SystemNotice> resultList = new List<SystemNotice>();
            var param = new DynamicParameters(new { });
            param.Add("continent", continent);
            param.Add("sellerid", sellerid.ToString());
            var sql = string.Format(@"select a.NoticeId,a.NoticeTitle,a.PublishTime,a.NoticeType,a.NoticeRollingConfig from Ymt_SystemNotice (nolock) a 
                                    join Ymt_SystemNotice_Target (nolock) as b on a.NoticeId=b.NoticeId 
                                    where a.IsDeleted=0 and  a.NoticeRollingConfig>0 and ( b.NoticeTargetDetail=@continent or b.NoticeTargetDetail=@sellerid ) order by a.publishtime desc");
            resultList = DapperHelper.QueryData<SystemNotice>(YmtSellerinfoReadConnectionString, sql, param);
            return resultList;
        }

        public List<SystemNotice> GetSellerAllNoticeList(string continent, int sellerid, int noticeType, int total = 10)
        {
            if (total == 0)
            {
                total = 10;
            }
            List<SystemNotice> resultList = new List<SystemNotice>();
            var param = new DynamicParameters(new { });
            param.Add("continent", continent);
            param.Add("sellerid", sellerid.ToString());
            param.Add("noticeType", noticeType);
            string sqlWhere = "";
            if (noticeType > 0)
            {
                sqlWhere = sqlWhere + " and a.NoticeType=@noticetype";
            }
            var sql = string.Format(@"select top {0} a.NoticeId,a.NoticeTitle,a.PublishTime,a.NoticeType,a.NoticeTitle ,(select count(1) from Ymt_SystemNotice_Read (nolock) where noticeid=a.noticeid and sellerid=@sellerid  ) as IsRead  from Ymt_SystemNotice (nolock) a 
                                    join Ymt_SystemNotice_Target (nolock) as b on a.NoticeId=b.NoticeId 
                                    where a.IsDeleted=0 and (b.NoticeTargetDetail=@continent or b.NoticeTargetDetail=@sellerid ) {1} order by a.publishtime desc", total, sqlWhere);

            resultList = DapperHelper.QueryData<SystemNotice>(YmtSellerinfoReadConnectionString, sql, param);
            return resultList;
        }

        public bool SubmitReadNotice(int sellerid, string noticeid,string resource)
        {
            var sql =
                @"if exists(select 1 from Ymt_SystemNotice_Read (nolock) where sellerid=@sellerid and noticeid=@noticeid )
                           UPDATE Ymt_SystemNotice_Read 
                           SET lastupdatetime=getdate() 
                         WHERE sellerid=@sellerid and noticeid=@noticeid 
                       else
                          insert into Ymt_SystemNotice_Read (NoticeId,SellerId,CreateTime,LastUpdateTime,resource) values (@noticeid,@sellerid,getdate(),getdate(),@resource)";

            return DapperHelper.ExcuteSql(YmtSellerinfoWriteConnectionString, sql,
                new
                {
                    sellerid = sellerid,
                    noticeid = noticeid,
                    resource = resource
                });
        }

        /// <summary>
        /// 获取通知消息
        /// </summary>
        /// <param name="noticeTitle"></param>
        /// <param name="noticeType"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<SystemNoticeDetail> GetSystemNoticeDetailList(int sellerid, string continent, NoticeTypeEnum noticeType, DateTime? beginTime, DateTime? endTime, int pageIndex, int pageSize, out int totalCount)
        {
            totalCount = 0;
            var param = new DynamicParameters(new { });
            var start = (pageIndex - 1) * pageSize + 1;
            var end = pageIndex * pageSize;
            param.Add("StartRow", start);
            param.Add("EndRow", end);

            List<SystemNoticeDetail> result = new List<SystemNoticeDetail>();
            string queryWhere = "";
            if (noticeType > 0)
            {
                queryWhere += " AND a.NoticeType=@NoticeType ";
                param.Add("NoticeType", noticeType);
            }
            if (beginTime.HasValue)
            {
                queryWhere += " AND a.CreateTime>=@BeginTime ";
                param.Add("BeginTime", beginTime.Value);
            }
            if (endTime.HasValue)
            {
                queryWhere += " AND a.CreateTime<=@EndTime ";
                param.Add("EndTime", endTime.Value);
            }
            if (sellerid > 0 && !string.IsNullOrWhiteSpace(continent))
            {
                queryWhere += " AND (b.NoticeTargetDetail=@continent or b.NoticeTargetDetail=@sellerid )";
                param.Add("continent", continent);
                param.Add("sellerid", sellerid.ToString());
            }
            var tmpSql =
                string.Format(@" SELECT T2.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY CreateTime DESC) RowNum ,T1.* FROM (SELECT a.NoticeId
                                              ,[NoticeTitle]
                                              ,[NoticeType]
                                              ,[NoticeTarget]
                                              ,[NoticeUrl]
                                              ,[NoticeContext]
                                              ,[NoticeRollingConfig]
                                              ,[Operator]
                                              ,[PublishTime]
                                              ,a.IsDeleted
                                              ,a.CreateTime
                                              ,a.LastUpdateTime 
                                              ,(select count(1) from Ymt_SystemNotice_Read (nolock) where noticeid=a.noticeid and sellerid=@sellerid  ) as IsRead  
                                          FROM [dbo].[Ymt_SystemNotice] as a WITH(NOLOCK) join Ymt_SystemNotice_Target as b (nolock) 
                                               on a.noticeid=b.noticeid
                                          WHERE a.isdeleted=0  {0}) T1) T2 WHERE RowNum BETWEEN @StartRow AND @EndRow; ",
                    queryWhere);

            var totalCountSql =
                string.Format("SELECT COUNT(0)  FROM [dbo].[Ymt_SystemNotice] as a WITH(NOLOCK)  join Ymt_SystemNotice_Target (nolock) as b on a.noticeid=b.noticeid WHERE a.isdeleted=0 {0};", queryWhere);
            try
            {
                using (var conn = new System.Data.SqlClient.SqlConnection(YmtSellerinfoReadConnectionString))
                {
                    var multi = conn.QueryMultiple(totalCountSql + tmpSql, param);
                    totalCount = multi.Read<int>().FirstOrDefault();
                    result = multi.Read<SystemNoticeDetail>().ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// 获取通知类型下面总的未读数
        /// </summary>
        /// <param name="sellerid"></param>
        /// <param name="continent"></param>
        /// <param name="noticeType"></param>
        /// <returns></returns>

        public int GetNoticeTypeUnReadCount(int sellerid, string continent, int noticeType)
        {
            string queryWhere = "";
            int retvalue = 0;
            var param = new DynamicParameters(new { });
            if (sellerid > 0 && !string.IsNullOrWhiteSpace(continent))
            {
                queryWhere += " AND (b.NoticeTargetDetail=@continent or b.NoticeTargetDetail=@sellerid )";
                param.Add("continent", continent);
                param.Add("sellerid", sellerid.ToString());
            }
            param.Add("NoticeType", noticeType);
            param.Add("Isellerid", sellerid);
            var totalCountSql =
              string.Format("SELECT COUNT(1)  FROM [dbo].[Ymt_SystemNotice] as a WITH(NOLOCK)  join Ymt_SystemNotice_Target (nolock) as b on a.noticeid=b.noticeid WHERE a.isdeleted=0  " +
                            " and a.NoticeType=@NoticeType and not exists(select 1 from  Ymt_SystemNotice_Read (nolock) where noticeid=a.noticeid and sellerid=@Isellerid  )  {0};", queryWhere);
            using (var conn = new System.Data.SqlClient.SqlConnection(YmtSellerinfoReadConnectionString))
            {
                var multi = conn.QueryMultiple(totalCountSql, param);
                retvalue = multi.Read<int>().FirstOrDefault();
            }
            return retvalue;
        }


        #endregion
    }
}
